<?php
namespace core\db;

use core\db\Database;
use PDO;

/**
 * 查询构建器类
 * 支持链式调用构建SQL查询
 */
class QueryBuilder {
    // 数据库连接
    private $connection;
    // 表名
    private $table;
    // 查询字段
    private $fields = ['*'];
    // WHERE条件
    private $wheres = [];
    // 排序
    private $orderBys = [];
    // 限制
    private $limit = null;
    // 偏移
    private $offset = null;
    // JOIN语句
    private $joins = [];
    // 参数绑定
    private $params = [];

    /**
     * 构造函数
     * @param string $table 表名
     */
    public function __construct($table) {
        $this->table = $table;
        $this->connection = Database::getInstance()->getConnection();
    }

    /**
     * 设置查询字段
     * @param mixed $fields 字段名，可以是字符串或数组
     * @return $this
     */
    public function select($fields) {
        $this->fields = is_array($fields) ? $fields : func_get_args();
        return $this;
    }

    /**
     * 添加WHERE条件
     * @param string $field 字段名
     * @param mixed $value 值
     * @param string $operator 操作符
     * @return $this
     */
    public function where($field, $value = null, $operator = '=') {
        // 处理数组条件
        if (is_array($field)) {
            foreach ($field as $key => $val) {
                $this->where($key, $val);
            }
            return $this;
        }

        // 处理闭包条件（用于分组条件）
        if (is_callable($field)) {
            $query = new static($this->table);
            $field($query);
            $this->wheres[] = ['type' => 'group', 'query' => $query];
            return $this;
        }

        // 标准条件
        $this->wheres[] = [
            'type' => 'basic',
            'field' => $field,
            'operator' => $operator,
            'value' => $value,
            'boolean' => 'and'
        ];
        $this->params[] = $value;
        return $this;
    }

    /**
     * 添加OR WHERE条件
     * @param string $field 字段名
     * @param mixed $value 值
     * @param string $operator 操作符
     * @return $this
     */
    public function orWhere($field, $value = null, $operator = '=') {
        // 处理数组条件
        if (is_array($field)) {
            foreach ($field as $key => $val) {
                $this->orWhere($key, $val);
            }
            return $this;
        }

        // 标准条件
        $this->wheres[] = [
            'type' => 'basic',
            'field' => $field,
            'operator' => $operator,
            'value' => $value,
            'boolean' => 'or'
        ];
        $this->params[] = $value;
        return $this;
    }

    /**
     * 添加ORDER BY
     * @param string $column 列名
     * @param string $direction 排序方向
     * @return $this
     */
    public function orderBy($column, $direction = 'asc') {
        $this->orderBys[] = [$column, strtoupper($direction)];
        return $this;
    }

    /**
     * 添加LIMIT
     * @param int $limit 数量
     * @return $this
     */
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }

    /**
     * 添加OFFSET
     * @param int $offset 偏移量
     * @return $this
     */
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }

    /**
     * 添加JOIN
     * @param string $table 表名
     * @param string $first 第一个字段
     * @param string $operator 操作符
     * @param string $second 第二个字段
     * @param string $type JOIN类型
     * @return $this
     */
    public function join($table, $first, $operator, $second, $type = 'inner') {
        $this->joins[] = [
            'type' => strtoupper($type),
            'table' => $table,
            'first' => $first,
            'operator' => $operator,
            'second' => $second
        ];
        return $this;
    }

    /**
     * 获取所有记录
     * @return array
     */
    public function get($cacheMinutes = 0) {
        $sql = $this->buildSelect();
        
        // 如果启用缓存
        if ($cacheMinutes > 0) {
            $cacheKey = 'query_' . md5($sql . implode(',', $this->params));
            $result = \core\cache\CacheManager::get($cacheKey);
            
            if ($result !== null) {
                return $result;
            }
        }
        
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($this->params);
        $result = $stmt->fetchAll();
        
        // 缓存查询结果
        if ($cacheMinutes > 0) {
            \core\cache\CacheManager::set($cacheKey, $result, $cacheMinutes * 60);
        }
        
        return $result;
    }

    /**
     * 获取单条记录
     * @return array|null
     */
    public function first() {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }

    /**
     * 添加WHERE IN条件
     * @param string $column 字段名
     * @param array $values 值数组
     * @return $this
     */
    public function whereIn($column, array $values) {
        $placeholders = implode(',', array_fill(0, count($values), '?'));
        $this->wheres[] = [
            'type' => 'basic',
            'field' => $column,
            'operator' => 'IN',
            'value' => '(' . $placeholders . ')',
            'boolean' => 'and'
        ];
        $this->params = array_merge($this->params, $values);
        return $this;
    }
    
    // 删除第二个重复的orWhere()方法（第206-214行）
    // 添加事务支持
    public function transaction(callable $callback) {
        $db = Database::getInstance();
        try {
            $db->beginTransaction();
            $result = $callback($this);
            $db->commit();
            return $result;
        } catch (\Exception $e) {
            $db->rollBack();
            throw $e;
        }
    }

    /**
     * 构建WHERE条件SQL
     * @return string
     */
    private function buildWhere() {
        if (empty($this->wheres)) {
            return '';
        }

        $sql = ' WHERE';
        $first = true;

        foreach ($this->wheres as $where) {
            if ($first) {
                $first = false;
            } else {
                $sql .= ' ' . $where['boolean'];
            }

            if ($where['type'] === 'basic') {
                $sql .= ' ' . $where['field'] . ' ' . $where['operator'] . ' ?';
            } elseif ($where['type'] === 'in') {
                $sql .= ' ' . $where['field'] . ' IN (' . implode(', ', $where['values']) . ')';
            } elseif ($where['type'] === 'group') {
                $groupSql = $where['query']->buildWhere();
                if ($groupSql) {
                    $sql .= ' (' . substr($groupSql, 6) . ')'; // 移除WHERE前缀
                    $this->params = array_merge($this->params, $where['query']->params);
                }
            }
        }

        return $sql;
    }

    /**
     * 构建SELECT语句
     * @return string
     */
    private function buildSelect() {
        return $this->toSql();
    }

    /**
     * 构建SQL语句
     * @return string
     */
    public function toSql() {
        $sql = 'SELECT ' . implode(', ', $this->fields) . ' FROM ' . $this->table;

        // 添加JOIN
        foreach ($this->joins as $join) {
            $sql .= ' ' . $join['type'] . ' JOIN ' . $join['table'] . ' ON ' . $join['first'] . ' ' . $join['operator'] . ' ' . $join['second'];
        }

        // 添加WHERE条件
        $sql .= $this->buildWhere();

        // 添加ORDER BY
        if (!empty($this->orderBys)) {
            $sql .= ' ORDER BY ';
            $orderClauses = [];
            foreach ($this->orderBys as $orderBy) {
                $orderClauses[] = $orderBy[0] . ' ' . $orderBy[1];
            }
            $sql .= implode(', ', $orderClauses);
        }

        // 添加LIMIT
        if ($this->limit !== null) {
            $sql .= ' LIMIT ' . $this->limit;
        }

        // 添加OFFSET
        if ($this->offset !== null) {
            $sql .= ' OFFSET ' . $this->offset;
        }

        return $sql;
    }
}